package iot.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;

import com.sun.istack.internal.NotNull;

import iot.bean.Thistorydata;
import iot.utils.ConnDb;

public class HistorydataDao {
	private Connection cn = null;
	private PreparedStatement ps = null;
	private ResultSet rs = null;
	//public static final int PAGE_LENGTH = 5;// 分页显示查询结果时，每页显示的记录数

	public void closedb() {
		try {
			if (rs != null) {
				rs.close();
			}
			if (ps != null) {
				ps.close();
			}
			if (cn != null) {
				cn.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public Thistorydata last(@NotNull String likeTag) {
		Thistorydata ret = new Thistorydata();
		cn = new ConnDb().getcon();
		String sqlstr = "select * from thistorydata where recorddata like '%" + likeTag + "%' order by id desc limit 1";
		System.out.println(sqlstr);
		try {
			ps = cn.prepareStatement(sqlstr);
			rs = ps.executeQuery();
			while(rs.next()) {
				ret.setId(rs.getLong("id"));
				ret.setGdid(rs.getLong("gdid"));
				ret.setRecordtime(rs.getTimestamp("recordtime"));
				ret.setRecorddata(rs.getString("recorddata"));
				ret.setRemark(rs.getString("remark"));
			}
		} catch (SQLException e) {
			ret = null;
			e.printStackTrace();
		} finally {
			this.closedb();
		}
		return ret;
	}

	// 查询,未分页
	public ArrayList<Thistorydata> query(String startTime, String endTime, @NotNull String likeTag) {
		ArrayList<Thistorydata> retlist = new ArrayList<Thistorydata>();
		try {
			cn = new ConnDb().getcon();
			String sqlstr = "select * from (select * from thistorydata where recorddata like '%" + likeTag + "%' order by id desc limit 10)a order by id asc";
			if (startTime != null & !"".equals(startTime)) {
				sqlstr = "select * from thistorydata where recordtime>='"
						+ startTime + "' and recordtime<='" + endTime
						+ "' and recorddata like '%" + likeTag + "%';";
			}
			System.out.println(sqlstr);
			ps = cn.prepareStatement(sqlstr);
			rs = ps.executeQuery();
			while (rs.next()) {
				Thistorydata temp = new Thistorydata();
				temp.setId(rs.getLong("id"));
				temp.setGdid(rs.getLong("gdid"));
				temp.setRecordtime(rs.getTimestamp("recordtime"));
				temp.setRecorddata(rs.getString("recorddata"));
				temp.setRemark(rs.getString("remark"));
				retlist.add(temp);
			}
		} catch (Exception e) {
			retlist = null;
			e.printStackTrace();
		} finally {
			this.closedb();
		}
		return retlist;
	}
	public ArrayList<Thistorydata> query(@NotNull String likeTag) {
		return query(null, null, likeTag);
	}

	/*// 分页查询，见对应知识点文档
	public ArrayList<Thistorydata> queryPage(String conStr, int page) {
		ArrayList<Thistorydata> retlist = new ArrayList<Thistorydata>();
		try {
			cn = new ConnDb().getcon();
			int begin = (page - 1) * PAGE_LENGTH;
			// begin 是指 page 所在页面的起始记录号，初始值为 0，page 可理解为当前页，PAGE_LENGTH 每页显示的记录数
			String sqllimit = " order by id desc limit " + begin + "," + PAGE_LENGTH;
			// 查询结果按 id 降序排列，并增加分页显示的条件 limit begin, PAGE_LENGTH
			String sqlstr = "select * from thistorydata " + sqllimit;
			if (conStr != "") {
				sqlstr = "select * from thistorydata where " + conStr
						+ sqllimit;
			}
			// System.out.println(sqlstr);
			ps = cn.prepareStatement(sqlstr);
			rs = ps.executeQuery();
			while (rs.next()) {
				Thistorydata temp = new Thistorydata();
				temp.setId(rs.getLong("id"));
				temp.setGdid(rs.getLong("gdid"));
				temp.setRecordtime(rs.getTimestamp("recordtime"));
				temp.setRecorddata(rs.getString("recorddata"));
				temp.setRemark(rs.getString("remark"));
				retlist.add(temp);
			}
		} catch (Exception e) {
			retlist = null;
			e.printStackTrace();
		} finally {
			this.closedb();
		}
		return retlist;
	}*/

	public int count(String startTime, String endTime, String likeTag) {
		// 计算查询结果的总记录数，以确定结果显示需要多少页面
		int n = 0;
		try {
			cn = new ConnDb().getcon();
			String sqlstr = "select count(*) from thistorydata ";
			if (startTime != ""&&endTime != "") {
				sqlstr = "select count(*) from thistorydata where recordtime>='" + startTime
						+ "' and recordtime<='"	+ endTime
						+ "' and recorddata like '%" + likeTag + "%';";
			}
			// System.out.println(sqlstr);
			ps = cn.prepareStatement(sqlstr);
			rs = ps.executeQuery();
			if (rs.next()) {
				n = rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			this.closedb();
		}
		return n;
	}

	// 数据添加
	public boolean add(Thistorydata historydata) {
		boolean addFlag = false;
		try {
			cn = new ConnDb().getcon();
			String sqlstr = "insert into thistorydata(recordtime,recorddata,remark) values(?,?,?) ";
			ps = cn.prepareStatement(sqlstr);
			//ps.setLong(2, historydata.getGdid());
			ps.setTimestamp(1, new Timestamp(System.currentTimeMillis()));
			ps.setString(2, historydata.getRecorddata());
			ps.setString(3, historydata.getRemark());
			int rows = ps.executeUpdate();
			if (rows > 0) {
				addFlag = true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			this.closedb();
		}
		return addFlag;
	}

	// 删除记录
	public boolean delete(String number) {
		boolean delFlag = false;
		try {
			cn = new ConnDb().getcon();
			String sqlstr = "delete from thistory where id=" + number + " ";
			ps = cn.prepareStatement(sqlstr);
			int rows = ps.executeUpdate();
			if (rows > 0) {
				delFlag = true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			this.closedb();
		}
		return delFlag;
	}
}
